﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_cm_ListSmallGroup')
BEGIN
    PRINT 'Dropping Procedure proc_cm_ListSmallGroup'
    DROP  Procedure  proc_cm_ListSmallGroup
END
GO

PRINT 'Creating Procedure proc_cm_ListSmallGroup'
GO

CREATE PROCEDURE [dbo].[proc_cm_ListSmallGroup]
AS
BEGIN
	SET NOCOUNT ON

	SELECT 
		sg.[smallgroup_id]
		,[group_nm]
		,[group_leader_id]
		,[group_leader_nm]
		,[group_leader_email]
		,[group_leader_phone]
		,[description_txt]
		,count(msg.[member_id]) as [member_cnt]
	FROM
		[dbo].[tbl_smallgroup] sg (NOLOCK)
		LEFT JOIN [dbo].[tbl_member_smallgroup] msg (NOLOCK) ON sg.smallgroup_id = msg.smallgroup_id
	GROUP BY
		sg.[smallgroup_id]
		,[group_nm]
		,[group_leader_id]
		,[group_leader_nm]
		,[group_leader_email]
		,[group_leader_phone]
		,[description_txt]
	ORDER BY
		[group_nm] ASC
END
GO

GRANT EXEC ON dbo.proc_cm_ListSmallGroup TO PUBLIC
GO
